package net.aaa.hotel.dao.impl;

import net.aaa.hotel.dao.YGDao;
import net.aaa.hotel.entity.HYg;
import net.aaa.hotel.util.BaseDB;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class IYGDaoImpl implements YGDao {
    @Override
    public List<Map> queryAll() {
        String sql =  "select a.id,a.name ,a.rzdate,a.address,a.status,c.name as rname,b.name as dname from h_yg a,t_dept b,h_role c where a.jueseid=c.id and a.deptid=b.id and a.status=0";
        return BaseDB.queryMap(sql);
    }


    @Override
    public List<Map> queryOne(String name) {
        String sql = "select * from h_yg where name=?";
        return BaseDB.queryMap(sql,name);
    }

    @Override
    public int addYg(HYg yg) {
        if (yg.getId() == null) {
            String sql = "insert into h_yg (name,rzdate,address,jueseid,deptid) values(?,?,?,?,?)";
            return BaseDB.update(sql,yg.getName(),yg.getRzdate(),yg.getAddress(),yg.getJueseid(),yg.getDeptid());
        }else {
            String sql = "update  h_yg set name=?,address=?,lzdate=?,status=?,jueseid=?,deptid=? where id=?";
            return BaseDB.update(sql,yg.getName(),yg.getAddress(),yg.getLzdate(),yg.getStatus(),yg.getJueseid(),yg.getDeptid(),yg.getId());
        }

    }

    @Override
    public int delYG(String gid) {
        String sql = "delete from h_yg where id =?";
        return BaseDB.update(sql,gid);
    }
    @Override
    public List<Map> findUserByNameAndPaddword(String name,String password) {
        String sql = "select * from h_yg where name=? and pwd=?";
        return BaseDB.queryMap(sql,name,password);
    }

    @Override
    public List<Map> getAllByPage(Integer curr, Integer nums, String condition) {
        String sql="select a.id,a.name ,a.rzdate,a.address,a.status,c.name as rname,b.name as dname from h_yg a,t_dept b,h_role c where a.jueseid=c.id and a.deptid=b.id and a.status=0 ";
        if (condition!=null){
            sql+=condition;
        }
        sql+=" limit ?,? ";
        int start =(curr-1)*nums;
        return BaseDB.queryMap(sql,start,nums);
    }

    @Override
    public int getCount(String condition) {
        String sql="select count(*) a from ( " +
                "select a.id,a.name ,a.rzdate,a.address,a.status,c.name as rname,b.name as dname from h_yg a,t_dept b,h_role c where a.jueseid=c.id and a.deptid=b.id and a.status=0 ";
        if(condition!=null){
            sql+=condition;
        }
        sql+=" ) b ";

        Object a = BaseDB.queryMap(sql).get(0).get("a") + "";
        return Integer.parseInt(a+"") ;
    }

    @Override
    public  List<Map> queryMing(String student) {
        String sql =  "select a.id,a.name ,a.rzdate,a.address,a.status,c.name as rname,a.imgpath,a.iphone,a.idcard,b.name as dname from h_yg a,t_dept b,h_role c where a.jueseid=c.id and a.deptid=b.id and a.`name`=?";

        return BaseDB.queryMap(sql,student);
    }

    @Override
    public int addImg(HYg yg) {
        String sql = "update  h_yg set address=?,iphone=?,imgpath=? where id=?";
        return BaseDB.update(sql,yg.getAddress(),yg.getIphone(),yg.getImgpath(),yg.getId());

    }

    @Override
    public int updM(HYg yg) {
        String sql = "update  h_yg set pwd=? where id=?";
        return BaseDB.update(sql,yg.getPwd(),yg.getId());
    }

    @Override
    public List<Map> queryType() {
        String sql = "select id,name,bieming from t_type";
        return BaseDB.queryMap(sql);
    }

    @Override
    public List<Double> queryMoney(Map map) {
        // 定义默认起始月
        int minMonth = 1;
        // 定义默认结束月
        int maxMonth = 12;

        List<Double> manageMoney = new ArrayList<>();
        String sql ="SELECT MONTH(tz.indate) month,SUM(tz.rzmoney) money FROM t_type tp,t_order tr,t_ruzhu tz WHERE tp.id=tr.roomtype AND tr.id=tz.orderid AND tp.id=?  and year(tz.indate) = 2020  and month(tz.indate) >= 1  and month(tz.indate) <=12 GROUP BY month(tz.indate)";
        List<Map> maps = BaseDB.queryMap(sql, map.get("id"));
        for (int i = minMonth; i <= maxMonth; i++) {
            double value = 0;
            for (Map map1 : maps) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ruKuMoney对应的月份的位置
                if (Integer.parseInt(map1.get("month")+"")==i){
                   // map2.put("motnh"+i,map1.get("money")+"");
                    value =Double.parseDouble(map1.get("money")+"") ;
                }
            }
           manageMoney.add(value);
        }

        return manageMoney;
    }
    @Override
    public Map qeuryY(Map map) {
        // 定义默认起始月
        int minMonth = 1;
        // 定义默认结束月
        int maxMonth = 12;
        HashMap<Object, Object> ma = new HashMap<>();
        String sql ="SELECT MONTH(tz.indate) month,SUM(tz.rzmoney) money FROM t_type tp,t_order tr,t_ruzhu tz WHERE tp.id=tr.roomtype AND tr.id=tz.orderid AND tp.id=?  and year(tz.indate) = 2020  and month(tz.indate) >= 1  and month(tz.indate) <=12 GROUP BY month(tz.indate)";
        List<Map> maps = BaseDB.queryMap(sql, map.get("id"));
        for (int i = minMonth; i <= maxMonth; i++) {
            double value = 0;
            for (Map map1 : maps) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ruKuMoney对应的月份的位置
                if (Integer.parseInt(map1.get("month")+"")==i){
                    ma.put("month"+i,map1.get("money")+"元");

                }
            }

        }



        return ma;
    }

    @Override
    public List<Map> GetQuanXian(int rid) {
        String sql = "SELECT tm.* FROM h_role_menu hm,t_menu tm WHERE hm.mid=tm.mid and tm.mpid=0 and hm.rid=?";
        return BaseDB.queryMap(sql,rid);
    }

    @Override
    public List<Map> quanEr(int rid,Object mid) {
        String sql = "SELECT * FROM t_menu m left join h_role_menu r on m.mid=r.mid where r.rid=? and m.mpid=?";

        return BaseDB.queryMap(sql,rid,mid);
    }

    @Override
    public List<Object> queryMouth() {
        // 定义默认起始月
        int minMonth = 1;
        // 定义默认结束月
        int maxMonth = 12;
        // 装填月份的list
        List<Object> monthData = new ArrayList<>();

        for (int i = minMonth; i <= maxMonth; i++) {
            monthData.add(i+"月");
        }
        return monthData;
    }




}























